package cn.edu.hizee.gps.dao;

import cn.edu.hizee.gps.model.Site;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author ZX
 */
public interface SiteRepository extends JpaRepository<Site,Integer>, CrudRepository<Site,Integer> {

    /**
     * 查看遇害人员
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT " +
            "t_site.id, " +
            "t_site.user_id, " +
            "t_site.lng, " +
            "t_site.lat, " +
            "t_user.phone, " +
            "t_user.real_name, " +
            "t_user.nickname " +
            "FROM " +
            "t_site  " +
            "JOIN t_user on t_user.id = t_site.user_id")
    List<Map> findAllDangerPeople();


    @Query(nativeQuery = true, value = "SELECT " +
            "t_site.id, " +
            "t_site.user_id, " +
            "t_site.lng, " +
            "t_site.lat, " +
            "t_user.phone, " +
            "t_user.real_name, " +
            "t_user.nickname " +
            "FROM " +
            "t_site  " +
            "JOIN t_user on t_user.id = t_site.user_id " +
            "LIMIT :pageNum, :pageAmount")
    List<Map> findDangerPeoplePage(int pageNum, int pageAmount);

    /**
     * 计数
     * @param userId
     * @return
     */
    Integer countSitesByUserId(Integer userId);

    /**
     * 删除
     * @param userId
     * @return
     */
    @Modifying
    @Transactional
    @Query(nativeQuery = true,value = "delete from t_site where t_site.user_id = :userId")
    Integer deleteByUserId(@Param("userId") Integer userId);

    /**
     * 更新遇害人员信息
     * @param userId
     * @param lng
     * @param lat
     */
    @Transactional
    @Modifying(clearAutomatically = true)
    @Query(nativeQuery = true,value = "UPDATE t_site SET t_site.lng = :lng,t_site.lat = :lat WHERE t_site.user_id = :userId")
    void updateSite(@Param("userId") Integer userId, @Param("lng") String lng, @Param("lat") String lat);

    /**
     * 查找星期数以及对应星期
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT " +
            "COUNT(*), " +
            "t_site.extra " +
            "FROM t_site " +
            "GROUP BY t_site.extra")
    List<Map<String,Object>> findCountAndExtra();
}
